package httpclient;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.Rows;

import com.swf.seed.base.SdCharset;
import com.swf.seed.base.SystemContants;
import com.swf.seed.regex.PatternUtils;
import com.swf.seed.triutil.lang.SdFileUtils;
import com.swf.seed.triutil.lang.SdIOUtils;
import com.swf.seed.xls.sheet.SheetUtils;
class Table{
	private String tableName;
	private String priKey;
	private Set<String> fieldStrSet;
	public Table() {
	}
	public Table(String tableName) {
		super();
		this.tableName = tableName;
		this.fieldStrSet = new LinkedHashSet<String>();
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public Set<String> getFieldStrList() {
		return fieldStrSet;
	}
	public void setFieldStrList(Set<String> fieldStrSet) {
		this.fieldStrSet = fieldStrSet;
	}
	
	public void addFieldStrList(String fieldStr){
		fieldStrSet.add(fieldStr);
	}
	
	public void setPriKey(String priKey) {
		this.priKey = priKey;
	}
	
	public String getPriKey() {
		return priKey;
	}
}

public class BoaHttpClient {
	public static void main(String[] args) throws IOException {
		File file = SdFileUtils.getFile("D:/linux/monitor.sql");
		SdIOUtils io = SdIOUtils.getInstance();
		io.setInFile(file);
		SdIOUtils setCharst = io.setCharst(SdCharset.UTF_8);
		List<String> lines = io.readByLine();
		List<Table> tableList = new ArrayList<Table>();
		Table table = new Table();
		String tableName;
		String fieldstr;
		String priKey;
		for (String line:lines){
			if (line.startsWith("CREATE TABLE")){
				tableName = PatternUtils.matchedFirstOne(line, "(?<=`)\\w+");
				table = new Table(tableName);
				tableList.add(table);
			}else if (line.startsWith("  `")){
				fieldstr = line.trim();
				int index = fieldstr.indexOf(',');
				if (index>0){
					fieldstr.substring(0,index);
				}
				table.addFieldStrList(fieldstr);
			}else if (line.contains("  PRIMARY KEY (`")){
				priKey = PatternUtils.matchedFirstOne(line, "(?<=`)\\w+(?=`)");
				table.setPriKey(priKey);
			}
		}
		HSSFWorkbook workbook = SheetUtils.createHSSFWorkbook();
		for (Table t:tableList){
			HSSFSheet sheet = SheetUtils.createHSSFSheet(t.getTableName(), workbook);
			HSSFRow row0 = sheet.createRow(0);
			HSSFCell cell = row0.createCell(0);
			cell.setCellValue("字段名");
			HSSFCell cell1 = row0.createCell(1);
			cell1.setCellValue("字段类型");
			HSSFCell cell2 = row0.createCell(2);
			cell2.setCellValue("字段长度");
			HSSFCell cell3 = row0.createCell(3);
			cell3.setCellValue("非空");
			HSSFCell cell4 = row0.createCell(5);
			cell4.setCellValue("默认值");
			HSSFCell cell5 = row0.createCell(6);
			cell5.setCellValue("中文名");
			HSSFCell cell6 = row0.createCell(7);
			cell6.setCellValue("字段描述");
			HSSFCell cell7 = row0.createCell(4);
			cell7.setCellValue("主键");
			Set<String> set = t.getFieldStrList();
			String fieldName = "";
			int i=1;
			String type;
			String lenth;
			String s;
			String[] fieldTypes = {"datetime","text","date","longtext"};
			boolean isNotNull;
			String defaultValue ="";
			String comment = "";
			String primaryKey;
			for (String fieldStr:set){
				HSSFRow row = sheet.createRow(i++);
				HSSFCell cel = row.createCell(0);
				isNotNull = fieldStr.contains("NOT NULL");
				fieldName = PatternUtils.matchedFirstOne(fieldStr, "(?<=`)\\w+");
				
				if (fieldStr.contains(" COMMENT")){
					comment = fieldStr.substring(fieldStr.indexOf("COMMENT")+8);
					comment = PatternUtils.matchedFirstOne(comment, "'.+(?=')").substring(1);
					fieldStr = fieldStr.substring(0,fieldStr.indexOf("COMMENT"));
				}
				if (fieldStr.contains(" DEFAULT")){
					defaultValue = fieldStr.substring(fieldStr.indexOf(" DEFAULT")+9);
					if (defaultValue.contains("NULL")){
						defaultValue = "NULL";
					}else{
						defaultValue = PatternUtils.matchedFirstOne(defaultValue, "'.*(?=')").substring(1);
					}
				}
				cel.setCellValue(fieldName.toLowerCase());
				s = PatternUtils.matchedFirstOne(fieldStr, "\\w+\\(\\d+(?=\\))");
				if (s==null){
					for (String fieldType:fieldTypes){
						if (fieldStr.contains(" "+fieldType)){
							s = fieldType+"(";
							break;
						}
					}
					if (s==null){
						continue;
					}
				}
				int index = s.indexOf('(');
				type = s.substring(0,index);
				
				lenth = s.substring(index+1);
				HSSFCell cel1 = row.createCell(1);
				cel1.setCellValue(type);
				HSSFCell cel2 = row.createCell(2);
				cel2.setCellValue(lenth);
				HSSFCell cel3 = row.createCell(3);
				cel3.setCellValue(isNotNull?"是":"否");
				HSSFCell cel4 = row.createCell(5);
				cel4.setCellValue(defaultValue);
				HSSFCell cel6 = row.createCell(7);
				cel6.setCellValue(comment);
				HSSFCell priKeycel = row.createCell(4);
				priKeycel.setCellValue(fieldName.equalsIgnoreCase(t.getPriKey())?"是":"");
				
			}
		}
		FileOutputStream os = new FileOutputStream(new File(SystemContants.PROJECT_PATH,"workbook.xls"));  
	    workbook.write(os);  
	    os.close();
	    for (Table t:tableList){
	    	System.out.println("truncate table "+t.getTableName()+";");
	    }
	}
}
